package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DBError;
import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.dialog.MessageBox;
import com.moleq.models.FlipChart;
import com.moleq.models.GlobalInfo;

public class FlipChartService {
	
	private DatabaseHelper DBHelper;
	private Context mContext;
	private String errorString;
	public static final int ALL = 0;
	public static final int UPC = 1;
	public static final int Department = 2;
	public static final int SubDept = 3;
	public static final int Vendor = 4;
	
	public FlipChartService(Context appContext, Context shareContext){
		mContext = appContext;
//		this.DBHelper = new DatabaseHelper(shareContext);
		this.DBHelper = new DatabaseHelper(GlobalInfo.getInstance().ShareContext);
	}
	
	public FlipChartService(Context context){
		this.DBHelper = new DatabaseHelper(context);
		mContext = context;
	}
	
	public List<FlipChart> getAllFlipChart(){
		String sql = "select a.fc_id,a.F01,a.fc_group,a.fc_order,a.create_datetime," +
				"a.update_datetime,b.F02 from t_flip_chart as a,t_item_setting as b " +
				"where a.F01 = b.F01 order by a.fc_order";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql,null);
//		Cursor cursor = DBHelper.rawQuery(sql,null);
		List<FlipChart> Source = new ArrayList<FlipChart>();
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return Source;
		}
		while (cursor.moveToNext()) {
			FlipChart fc = new FlipChart(
					cursor.getInt(0),
					cursor.getString(1),
					cursor.getInt(2),
					cursor.getInt(3),
					cursor.getString(4),
					cursor.getString(5),
					cursor.getString(6)
					);
			Source.add(fc);
		}
		cursor.close();
		return Source;
	}
	
	public int getRowCount(){
		int rowCount = 0;
		
		Cursor cursor = DBHelper.rawQuery("SELECT  count(*) FROM t_flip_chart", null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return rowCount;
		}
		if(cursor.moveToFirst()){
			 rowCount = cursor.getInt(0);
		}
		cursor.close();
		return rowCount;
	}
	
	public boolean insertFlipChart(FlipChart obj){
		boolean check = true;
		String sql,upc;
		int group,order;
		upc = obj.getUpc();
		group = obj.getGroup();
		order = obj.getOrder();
		
		sql = "INSERT INTO "+ DatabaseSchema.FlipChart.SCHEMA_NAME + " values";
		sql += "(null,'"+ upc +"',"+ group +","+ order +",strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'))";
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean updateOrder(int order,int id){
		boolean check = true;
		String sql;
		sql = "UPDATE " + DatabaseSchema.FlipChart.SCHEMA_NAME + " set fc_order = " + order+
				",update_datetime = strftime('%Y%m%d%H%M%S','now') where fc_id = " + id;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean deleteFCById(int id){
		boolean check = true;
		String sql;
		sql = "delete from "+ DatabaseSchema.FlipChart.SCHEMA_NAME + " where fc_id = " +id;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	public boolean deleteFCByUpc(String upc){
		boolean check = true;
		String sql;
		sql = "delete from "+ DatabaseSchema.FlipChart.SCHEMA_NAME + " where F01 = '" + upc + "'";
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public FlipChart FindFlipChartByUPC(String upc){
		String sql = "select * from "+ DatabaseSchema.FlipChart.SCHEMA_NAME + " where F01 = '" + upc + "'";
		Cursor cursor = DBHelper.rawQuery(sql, null);
		FlipChart mFlipChart = new FlipChart();
		if(cursor == null){
			return null;
		}
		while (cursor.moveToNext()) {
			mFlipChart.setId(cursor.getInt(0));
			mFlipChart.setUpc(cursor.getString(1));
			mFlipChart.setGroup(cursor.getInt(2));
			mFlipChart.setOrder(cursor.getInt(3));
			mFlipChart.setCreateTime(cursor.getString(4));
			mFlipChart.setUpdateTime(cursor.getString(5));
		}
		cursor.close();
		return mFlipChart;
	}
	
	public FlipChart FindFlipChartByID(int id){
		String sql = "select * from "+ DatabaseSchema.FlipChart.SCHEMA_NAME + " where fc_id = " + id;
		Cursor cursor = DBHelper.rawQuery(sql, null);
		FlipChart mFlipChart = new FlipChart();
		if(cursor == null){
			return null;
		}
		while (cursor.moveToNext()) {
			mFlipChart.setId(cursor.getInt(0));
			mFlipChart.setUpc(cursor.getString(1));
			mFlipChart.setGroup(cursor.getInt(2));
			mFlipChart.setOrder(cursor.getInt(3));
			mFlipChart.setCreateTime(cursor.getString(4));
			mFlipChart.setUpdateTime(cursor.getString(5));
		}
		cursor.close();
		return mFlipChart;
	}
	
	public boolean postUpdateSetOrder(int newOrder,int oldOrder,int id,int group){
		boolean check = true;
		String sql;
		if (newOrder < oldOrder) {
			sql = "UPDATE "+ DatabaseSchema.FlipChart.SCHEMA_NAME +" set fc_order = fc_order + 1," +
					"update_datetime = strftime('%Y%m%d%H%M%S','now') where fc_id <> " + id + " and fc_group = " + group +
					" and fc_order >= " + newOrder + " and fc_order < " + oldOrder;
		}else {
			sql = "UPDATE "+ DatabaseSchema.FlipChart.SCHEMA_NAME +" set fc_order = fc_order - 1," +
					"update_datetime = strftime('%Y%m%d%H%M%S','now') where fc_id <> " + id + " and fc_group = " + group +
					" and fc_order <= " + newOrder + " and fc_order > " + oldOrder;
		}
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean postDeleteSetOrder(int order,int group){
		boolean check = true;
		String sql;
		sql = "UPDATE "+ DatabaseSchema.FlipChart.SCHEMA_NAME +" set fc_order = fc_order - 1," +
				"update_datetime = strftime('%Y%m%d%H%M%S','now')" +
				" where fc_group = "+ group +" and fc_order > " + order;
		errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			check = false;
		}
		return check;
	}
	
	public boolean checkFlipChartInList(String upc){
		boolean result = false;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select F01 from "+ DatabaseSchema.FlipChart.SCHEMA_NAME +" where F01 = '"+ upc +"'", null);
//		Cursor cursor = DBHelper.rawQuery("select F01 from "+ DatabaseSchema.FlipChart.SCHEMA_NAME +" where F01 = '"+ upc +"'", null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return result;
		}
		if (cursor.moveToNext()) {
			result = true;
		}
		cursor.close();
		return result;
	}
	
	public int getMaxId(){
		int id = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select Max(fc_id) from " + DatabaseSchema.FlipChart.SCHEMA_NAME, null);
//		Cursor cursor = DBHelper.rawQuery("select Max(fc_id) from " + DatabaseSchema.FlipChart.SCHEMA_NAME, null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return id;
		}
		if (cursor.moveToNext()) {
			id = cursor.getInt(0);
			id ++;
		}
		cursor.close();
		return id;
	}
	
	public int GetCountFC(){
		int count = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from t_flip_chart as a," +
				"t_item_setting as b where a.F01 = b.F01 order by a.fc_order", null);
//		Cursor cursor = DBHelper.rawQuery("select count(*) from " + DatabaseSchema.FlipChart.SCHEMA_NAME, null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return count;
		}
		if (cursor.moveToNext()) {
			count = cursor.getInt(0);
		}
		cursor.close();
		return count;
	}
	
	public int getMaxOrder(){
		int order = 0;
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery("select Max(fc_order) from " + DatabaseSchema.FlipChart.SCHEMA_NAME, null);
//		Cursor cursor = DBHelper.rawQuery("select Max(fc_order) from " + DatabaseSchema.FlipChart.SCHEMA_NAME, null);
		if(cursor == null){
			MessageBox.show(mContext,DBError.error);
			DBError.error = "";
			return order;
		}
		if (cursor.moveToNext()) {
			order = cursor.getInt(0);
		}
		cursor.close();
		return order;
	}
}
